package com.javaweb.dao.impl;

import com.javaweb.aop.NPECatch;
import com.javaweb.dao.RoleDao;
import com.javaweb.model.Permission;
import com.javaweb.model.Role;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @desc:
 * @author: Ping
 * @date: 2024/9/24 22:44
 */

/**
 * todo:完成如下所有方法
 */

@Repository
@Slf4j
public class RoleDaoImpl implements RoleDao {
    @Autowired
    private Connection conn;

    @Override
    public Integer insert(Role role) {
        String sql = "INSERT INTO tb_role (name) VALUES(?)" ;
        try (
                PreparedStatement ps = this.conn.prepareStatement(sql)
        ) {
            ps.setString(1, role.getName());
            return ps.executeUpdate() ;
        } catch (SQLException e) {
            log.error("execute insert role error: " + e.getMessage()) ;
        }
        return 0 ;
    }

    @Override
    public Integer insertRolePerm(Role role, Permission perm) {
        String sql = "INSERT INTO tb_role_per (role_id, per_id) VALUES (?,?)" ;
        try (
                PreparedStatement ps = this.conn.prepareStatement(sql) ;) {
            ps.setInt(1, role.getRoleId()) ;
            ps.setInt(2, perm.getPerId()) ;
            return ps.executeUpdate() ;
        }catch (SQLException e) {
            log.error("execute insertRolePerm error: " + e.getMessage()) ;
        }
        return 0;
    }

    @NPECatch
    @Override
    public List<Role> selectRolesByUserName(String username) {
        if (username == null) {
            throw new IllegalArgumentException("username is null");
        }
        String sql = "SELECT r.* FROM tb_role r " +
                "JOIN tb_user_role ur ON ur.role_id = r.role_id " +
                "JOIN tb_user u ON u.user_id = ur.user_id " +
                "WHERE u.name = ?";
        List<Role> roles = new ArrayList<>();
        try (PreparedStatement ps = this.conn.prepareStatement(sql)) {
            ps.setString(1, username);  // 替换 SQL 语句中的占位符 ?
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Role role = new Role();
                role.setRoleId(rs.getInt(1));
                role.setName(rs.getString(2));
                roles.add(role);
            }
        } catch (SQLException e) {
            log.error("execute selectRolesByUserName error: " + e.getMessage()) ;
        }
        return roles;
    }

    @Override
    public List<Role> selectRolesByUserId(int userId) {
        String sql = "SELECT r.* FROM tb_role r " +
                "JOIN tb_user_role ur ON ur.role_id = r.role_id " +
                "JOIN tb_user u ON u.user_id = ur.user_id " +
                "WHERE u.user_id = ?";
        List<Role> roles = new ArrayList<>();
        try (PreparedStatement ps = this.conn.prepareStatement(sql)) {
            ps.setInt(1,userId); // 替换 SQL 语句中的占位符 ?
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Role role = new Role();
                role.setRoleId(rs.getInt(1));
                role.setName(rs.getString(2));
                roles.add(role);
            }
        } catch (SQLException e) {
            log.error("execute selectRolesByUserId error: " + e.getMessage()) ;
        }
        return roles;
    }

    @Override
    public void deleteByRoleId(int roleId) {
        String deleteUserRoleSQL = "DELETE FROM tb_user_role WHERE role_id = ?";
        String deleteRolePerSQL = "DELETE FROM tb_role_per WHERE role_id = ?";
        String deleteRoleSQL = "DELETE FROM tb_role WHERE role_id = ?";

        try (PreparedStatement ps1 = this.conn.prepareStatement(deleteUserRoleSQL);
             PreparedStatement ps2 = this.conn.prepareStatement(deleteRolePerSQL);
             PreparedStatement ps3 = this.conn.prepareStatement(deleteRoleSQL)) {

            // 设置 role_id 的参数
            ps1.setInt(1, roleId);
            ps2.setInt(1, roleId);
            ps3.setInt(1, roleId);

            // 开始事务,手动执行
            this.conn.setAutoCommit(false);

            // 执行删除操作
            ps1.executeUpdate();
            ps2.executeUpdate();
            ps3.executeUpdate();

            // 提交事务
            this.conn.commit();

        } catch (SQLException e) {
            // 如果出错，回滚事务
            try {
                if (this.conn != null) {
                    this.conn.rollback();
                }
            } catch (SQLException rollbackEx) {
                log.error("execute rollbackEx error: " + rollbackEx.getMessage()) ;
            }
            log.error("execute deleteByRoleId error: " + e.getMessage()) ;
        } finally {
            try {
                if (this.conn != null) {
                    this.conn.setAutoCommit(true);
                }
            } catch (SQLException ex) {
                log.error("execute AutoCommit error: " + ex.getMessage()) ;
            }
        }
    }

    @Override
    public void deleteUserRole(int userId, int roleId) {
        String sql = "DELETE FROM tb_user_role WHERE user_id = ? AND role_id = ?";

        try (PreparedStatement ps = this.conn.prepareStatement(sql)) {
            // 设置SQL语句中的参数
            ps.setInt(1, userId);  // 第一个占位符对应 userId
            ps.setInt(2, roleId);  // 第二个占位符对应 roleId

            // 执行删除操作
            int rowsAffected = ps.executeUpdate();
            if (rowsAffected > 0) {
                log.info("Successfully deleted user role.");
            } else {
                log.info("No matching user role found.");
            }
        } catch (SQLException e) {
            log.error("execute deleteUserRole error: " + e.getMessage()) ;
        }
    }
}
